﻿CREATE PROCEDURE dbo.SP_Nationalities_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @NationalityId Int   =  NULL 
, @NationalityName NVarChar (256)  =  NULL 
	AS
			WITH CTE_Nationalities AS
			( 
			SELECT
 							NationalityId,
								NationalityName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='NationalityId' THEN NationalityId
						 
						WHEN @OrderColume='NationalityName' THEN NationalityName
									ELSE 
							    NationalityId
					        	END
			) AS "RowNumber"
  			FROM Nationalities 
			where 
								(NationalityId = @NationalityId OR @NationalityId Is Null)
										AND(NationalityName LIKE @NationalityName OR @NationalityName Is Null)
								)
			SELECT  *
			FROM CTE_Nationalities
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			